package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import model_layer.Product;
import model_layer.ProductPart;
import model_layer.SaleLineItem;

public class ProductDB
{
	Connection con;
	public ProductDB()
	{
		con = DBConnection.get_instance().get_connection();
	}
	
	public Product find_product(String var_name, String var)
	{
		return single_find(var_name, var, -1);
	}
	
	public Product find_product(String var_name, int var)
	{
		return single_find(var_name, "", var);
	}
	
	public List<Product> find_all_products()
	{
		return multiple_find("", "", -1);
	}
	
	public List<Product> find_particular_products(String var_name, int var)
	{
		return multiple_find(var_name, "", var);
	}
	
	public boolean check_if_quantities_are_high_enough(List<SaleLineItem> items) throws SQLException
	{
		HashMap<Integer, Object[]> ids_to_amounts = new HashMap<>();
		for (SaleLineItem item : items)
		{
			ids_to_amounts.put(Integer.parseInt(item.getProduct().getId()), new Object[]{item.getAmount(), item.can_go_below_quantity()});
		}
		String vars = "";
		for(int i = 0; i < items.size(); i++)
		{
			if(i == 0)
			{
				vars += String.valueOf(items.get(i).getProduct().getId());
			}
			else
			{
				vars += ", " + String.valueOf(items.get(i).getProduct().getId());
			}
		}
		String query = String.format("SELECT id, quantity FROM Products WHERE id IN(%s)", vars);
		
		Statement ps = con.createStatement();
		ResultSet results = ps.executeQuery(query);
		while(results.next())
		{
			if((int) ids_to_amounts.get(results.getInt("id"))[0] > results.getInt("quantity") && !(boolean) ids_to_amounts.get(results.getInt("id"))[1])
			{
				return false;
			}
		}
		return true;

	}
	
	public void subtract_amounts(List<SaleLineItem> items) throws SQLException
	{
    	String query = "UPDATE Products SET quantity = quantity - %s WHERE id = %s";
    	String big_query = "";
    	for (SaleLineItem item : items)
    	{
    		big_query += String.format(query, item.getAmount(), Integer.parseInt(item.getProduct().getId()));
    	}
    	Statement stmt = con.createStatement();
    	stmt.setQueryTimeout(5);
    	stmt.executeUpdate(big_query);
	}
	
	public void update_amounts_and_prices(List<ProductPart> products) throws SQLException
	{
		String query = "UPDATE Products SET quantity = quantity + %s, last_retail_price = %s, price = %s WHERE id = %s";
		String big_query = "";
		for(ProductPart prod_part : products)
		{
			big_query += String.format(query, prod_part.getAmount(), prod_part.getRetail_price(),  prod_part.getProduct().getPrice(), Integer.parseInt(prod_part.getProduct().getId()));
		}
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		stmt.executeUpdate(big_query);
	}
	
	public int insert_product(Product prod)
	{
		int rc = -1;
		int id = Integer.parseInt(prod.getId());
		String name = prod.getName();
		int quantity = prod.getQuantity();
		int min_amount = prod.getMin_amount();
		float last_retail_price = prod.getLast_retail_price();
		float price = prod.getPrice();
		int deleted = prod.isDeleted() ? 1: 0;
		
		try
		{
			DBConnection.begin_transaction();
			PreparedStatement ps = con.prepareStatement("SELECT * FROM Products WHERE id = ? OR name = ?");
			ps.setInt(1, id);
			ps.setString(2, name);
			ps.setQueryTimeout(5);
			ResultSet results = ps.executeQuery();
			if(results.next())
			{
				System.out.println("ID or Name already present, terminating.");
				DBConnection.rollback_transaction();
				return -2;
			}
			ps.close();
			PreparedStatement prod_ps = DBUtilities.make_insert_statement(con, "Products", "id, name, quantity, min_amount, last_retail_price, price, deleted");
			prod_ps.setInt(1, id);
			prod_ps.setString(2, name);
			prod_ps.setInt(3, quantity);
			prod_ps.setInt(4, min_amount);
			prod_ps.setFloat(5, last_retail_price);
			prod_ps.setFloat(6, price);
			prod_ps.setInt(7, deleted);
			prod_ps.setQueryTimeout(5);
			rc = prod_ps.executeUpdate();
			prod_ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			System.err.println("Error while inserting product: " + e);
			DBConnection.rollback_transaction();
		}
		return rc;
	}
	
	public int update_product(Product prod)
	{
		int rc = -1;
		int id = Integer.parseInt(prod.getId());
		String name = prod.getName();
		int quantity = prod.getQuantity();
		int min_amount = prod.getMin_amount();
		float last_retail_price = prod.getLast_retail_price();
		float price = prod.getPrice();
		int deleted = prod.isDeleted() ? 1: 0;
		
		try
		{
			DBConnection.begin_transaction();
			String query = "SELECT id FROM Products";
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, "name", name);
			ps.setString(1, name);
			ps.setQueryTimeout(5);
			ResultSet results = ps.executeQuery();
			while(results.next())
			{
				if(results.getInt("id") != id)
				{
					System.out.println("Name already present, terminating.");
					DBConnection.rollback_transaction();
					return -2;
				}
			}
			ps.close();
			PreparedStatement prod_ps = DBUtilities.make_update_statement(con, "Products", "name, quantity, min_amount, last_retail_price, price, deleted", "id");
			prod_ps.setString(1, name);
			prod_ps.setInt(2, quantity);
			prod_ps.setInt(3, min_amount);
			prod_ps.setFloat(4, last_retail_price);
			prod_ps.setFloat(5, price);
			prod_ps.setInt(6, deleted);
			prod_ps.setInt(7, id);
			
			prod_ps.setQueryTimeout(5);
			rc = prod_ps.executeUpdate();
			prod_ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			System.err.println("Error while inserting product: " + e);
			DBConnection.rollback_transaction();
		}
		return rc;
	}
	
	private Product single_find(String var_name, String s_var, int i_var)
	{
		String query = make_product_query();
		ResultSet results;
		Product product = null;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				product = create_product(results);
			}
			ps.close();
			
		}
		catch(SQLException e)
		{
			System.err.println("Error while searching for product: " + e);
		}
		return product;
	}
	
	private List<Product> multiple_find(String var_name, String s_var, int i_var)
	{
		String query = make_product_query();
		ResultSet results;
		List<Product> products = new ArrayList<Product>();
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())
			{
				products.add(create_product(results));
			}
			ps.close();
		}
		catch(SQLException e)
		{
			products.clear();
			System.err.println("Products query exception: " + e);
		}
		return products;
	}
	
	private String make_product_query()
	{
		return DBUtilities.make_query("id, name, price, last_retail_price, quantity, min_amount, deleted", "Products");
	}
	
	private Product create_product(ResultSet results)
	{
		Product prod = new Product();
		try
		{
			prod.setId(String.valueOf(results.getInt("id")));
			prod.setMin_amount(results.getInt("min_amount"));
			prod.setName(results.getString("name"));
			prod.setPrice(results.getFloat("price"));
			prod.setLast_retail_price(results.getFloat("last_retail_price"));
			prod.setQuantity(results.getInt("quantity"));
			prod.setDeleted(results.getBoolean("deleted"));
		}
		catch(SQLException e)
		{
			System.out.println("Error while creating a product: " + e);
			prod = null;
		}
		return prod;
	}

}
